<?php
// 这里的是对于学生的成绩的操作
namespace App\Models;

use App\Config\Database;
use PDO;

class Score{ 
    private $conn;
    private $table = 'scores';
    private $student_table = 'students';
    private $course_table = 'courses';

    public function __construct() {
        //数据库的初始化
        $database = new Database();
        $this->conn = $database->getConnection();
    }

    /**
     * 获取学生的课程成绩
     * @param string $sid
     * @return array
     */
    public function getCourseScore($sid) {
        $query = "SELECT students.name AS student_name, courses.cid, courses.cname AS course_name, courses.credits, scores.score
            FROM " . $this->table . "
            JOIN " . $this->course_table . " ON " . $this->table . ".cid = " . $this->course_table . ".cid
            JOIN " . $this->student_table . " ON " . $this->table . ".sid = " . $this->student_table . ".sid
            WHERE " . $this->table . ".sid = :sid";
        $stmt = $this->conn->prepare($query);
        $stmt->bindValue(':sid', $sid, PDO::PARAM_STR);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * 获取学生的课程成绩
     * @param string $courseName 课程名称
     * @param string $major 专业名称
     * @return array
     */
    public function searchByCourseOrMajor($courseName = null, $major = null, $page = 1, $limit = 10) {
        // 参数校验，至少需要一个查询的条件
        if (!$courseName && !$major) {
            return [];
        }

        // 计算偏移量
        $offset = ($page - 1) * $limit;

        // 构建查询语句
        $query = "SELECT students.sid, students.name, scores.score, courses.cname AS course_name, courses.cid
                  FROM " . $this->table . "
                  JOIN " . $this->course_table . " ON " . $this->table . ".cid = " . $this->course_table . ".cid
                  JOIN " . $this->student_table . " ON " . $this->table . ".sid = " . $this->student_table . ".sid";

        $countQuery = "SELECT COUNT(*) as total
                       FROM " . $this->table . "
                       JOIN " . $this->course_table . " ON " . $this->table . ".cid = " . $this->course_table . ".cid
                       JOIN " . $this->student_table . " ON " . $this->table . ".sid = " . $this->student_table . ".sid";

        $conditions = [];
        $params = [];

        if (!empty($courseName)){
            $conditions[] = "courses.cname LIKE :courseName";
            $params[':courseName'] = '%' . $courseName . '%';
        }

        if (!empty($major)) {
            $conditions[] = "students.major LIKE :major";
            $params[':major'] = '%' . $major . '%';
        }

        if (!empty($conditions)) {
            $conditionStr = " WHERE " . implode(" AND ", $conditions);
            $query .= $conditionStr;
            $countQuery .= $conditionStr;
        }

        // 添加分页限制
        $query .= " LIMIT :limit OFFSET :offset";

        // 获取总数
        $countStmt = $this->conn->prepare($countQuery);
        foreach ($params as $param => $value) {
            $countStmt->bindValue($param, $value, PDO::PARAM_STR);
        }
        $countStmt->execute();
        $total = $countStmt->fetch(PDO::FETCH_ASSOC)['total'];

        // 获取分页数据
        $stmt = $this->conn->prepare($query);
        foreach ($params as $param => $value) {
            $stmt->bindValue($param, $value, PDO::PARAM_STR);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return [
            'data' => $data,
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'pages' => ceil($total / $limit)
        ];
    }

    /**
     * 更新学生的课程成绩
     * @param string $sid 学生ID
     * @param string $cid  课程ID
     * @param string $score 成绩
     * @return bool
     */
    public function updateScore($sid, $cid, $score) {
        // 验证成绩有效性
        if (!is_numeric($score) || $score < 0 || $score > 100) {
            throw new \InvalidArgumentException("成绩必须是0-100之间的数字");
        }
        
        $query = "UPDATE " . $this->table . " 
                  SET score = :score 
                  WHERE sid = :sid AND cid = :cid";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':score', $score, PDO::PARAM_STR);
        $stmt->bindParam(':sid', $sid, PDO::PARAM_STR);
        $stmt->bindParam(':cid', $cid, PDO::PARAM_STR);
        
        return $stmt->execute();
    }

    /**
     * 删除学生的课程成绩
     * @param string $sid 学生ID
     * @param string $cid  课程ID
     * @return bool
     */
    public function deleteScore($sid, $cid) {
        $query = "DELETE FROM " . $this->table . " 
                  WHERE sid = :sid AND cid = :cid";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':sid', $sid, PDO::PARAM_STR);
        $stmt->bindParam(':cid', $cid, PDO::PARAM_STR);
        
        return $stmt->execute();
    }
}

?>